package com.zxl.working.mapper.backend;

import com.zxl.working.entity.backend.AgentAccount;
import com.zxl.working.entity.backend.CpmInsideTrans;
import com.zxl.working.entity.backend.SellerAccount;
import org.apache.ibatis.annotations.*;
import org.springframework.beans.factory.annotation.Qualifier;

import java.util.List;

@Mapper
@Qualifier("backEndSqlSessionTemplate")
public interface TaskMapper {

    /**
     * 内部结算表插入
     * @param trans
     * @return
     */
    @Insert("<script>" +
            "insert into cpm_inside_trans(serial,sub_code,sub_type,order_id,order_source,trans_amount," +
            "direction,virtual_account,account_type,comment,seller_id,service_id,create_time,is_syn) VALUES" +
            "<foreach collection='trans' item='item' index='index' separator=','>" +
            "(#{item.serial},#{item.subCode},#{item.subType},#{item.orderId},#{item.orderSource},#{item.transAmount}," +
            "#{item.direction},#{item.virtualAccount},#{item.accountType},#{item.comment},#{item.sellerId},#{item.serviceId},now(),#{item.isSyn})" +
            "</foreach>" +
            "</script>")
    int batchInsertCpmInsideTrans(@Param("trans") List<CpmInsideTrans> trans);

    /**
     * 商户发放费用
     * @return
     */
    @Select("SELECT GetSerialNoFunc ( 'JS' ) as serial,'0001' as subCode,1 as subType,spo.order_id as orderId,'00' as orderSource," +
            "spo.real_amount as transAmount,-1 as direction,gaa.seller_account_id as virtualAccount,0 as accountType,spo.seller_id as sellerId," +
            "spo.service_id as serviceId,'N' AS isSyn " +
            "FROM seller_provide_order AS spo " +
            "LEFT JOIN get_account_by_seller AS gaa ON spo.seller_id = gaa.seller_id " +
            "WHERE spo.order_type = 0 AND spo.approve_status = 1")
    @Results(id = "transMap",value = {
        @Result(property = "serial",column = "serial"),
        @Result(property = "subCode",column = "subCode"),
        @Result(property = "subType",column = "subType"),
        @Result(property = "orderId",column = "orderId"),
        @Result(property = "orderSource",column = "orderSource"),
        @Result(property = "transAmount",column = "transAmount"),
        @Result(property = "direction",column = "direction"),
        @Result(property = "virtualAccount",column = "virtualAccount"),
        @Result(property = "accountType",column = "accountType"),
        @Result(property = "sellerId",column = "sellerId"),
        @Result(property = "serviceId",column = "serviceId")
    })
    List<CpmInsideTrans> qrySellerSendTrans();

    /**
     * 服务商服务费
     * @return
     */
    @Select("SELECT GetSerialNoFunc ( 'JS' ) as serial,'0002' as subCode,1 as subType,spo.order_id as orderId,'03' as orderSource," +
            "(spo.provide_amount - spo.real_amount) as transAmount,-1 as direction,gaa.seller_account_id as virtualAccount,0 as accountType,spo.seller_id as sellerId," +
            "spo.service_id as serviceId,'N' AS isSyn " +
            "FROM seller_provide_order AS spo " +
            "LEFT JOIN get_account_by_seller AS gaa ON spo.seller_id = gaa.seller_id " +
            "WHERE spo.order_type = 0 AND spo.approve_status = 1")
    @ResultMap(value = "transMap")
    List<CpmInsideTrans> qryServerFeeTrans();

    /**
     * 代理商月结佣金
     * @return
     */
    @Select("SELECT GetSerialNoFunc ( 'JS' ) as serial,'0004' as subCode,0 as subType,spo.order_id as orderId,'00' as orderSource," +
            "(spo.real_amount*(gaa.server_amount_point - gaa.base_amount_point)/100) as transAmount,1 as direction,gaa.agent_account_id as virtualAccount,1 as accountType,spo.seller_id as sellerId," +
            "spo.service_id as serviceId,'N' AS isSyn " +
            "FROM seller_provide_order AS spo " +
            "LEFT JOIN get_account_by_seller AS gaa ON spo.seller_id = gaa.seller_id " +
            "WHERE spo.order_type = 0 AND spo.approve_status = 1")
    @ResultMap(value = "transMap")
    List<CpmInsideTrans> qryAgentMonthFeeTrans();

    /**
     * 代理商阶梯佣金
     * @return
     */
    @Select("SELECT GetSerialNoFunc ( 'JS' ) as serial,'0005' as subCode,0 as subType,spo.order_id as orderId,'00' as orderSource," +
            "(spo.real_amount*gaa.step_amount_point/100) as transAmount,1 as direction,gaa.agent_account_id as virtualAccount,1 as accountType,spo.seller_id as sellerId," +
            "spo.service_id as serviceId,'W' AS isSyn " +
            "FROM seller_provide_order AS spo " +
            "LEFT JOIN get_account_by_seller AS gaa ON spo.seller_id = gaa.seller_id " +
            "WHERE spo.order_type = 0 AND spo.approve_status = 1")
    @ResultMap(value = "transMap")
    List<CpmInsideTrans> qryAgentStepFeeTrans();

    /**
     * 更新订单状态
     */
    @Update("update seller_provide_order set order_type = 1,end_time = now() where seller_id in (select seller_id from get_account_by_seller) and order_type = 0 and approve_status = 1")
    int updSellerProvideOrder();

    /**
     * 插入商户明细表
     */
    @Insert("INSERT INTO seller_account_detail ( trade_id, seller_account_id, trade_serial, order_source, oper_type, " +
            "pay_time, pay_subject, pay_type, trade_money, trade_comment, is_total, cpm_time, seller_id, service_id ) " +
            "SELECT serial,virtual_account,order_id,order_source,direction,create_time,sub_code,sub_type,trans_amount,`COMMENT`,'N',now()," +
            "seller_id,service_id FROM cpm_inside_trans WHERE account_type = 0 AND is_syn = 'N'")
    int insSellerProvideOrder();

    /**
     * 更新内部结算表
     */
    @Update("update cpm_inside_trans set update_time = now(),is_syn = 'Y' where account_type = #{accountType} and is_syn = 'N'")
    int updInsideTrans(@Param("accountType") Integer accountType);

    /**
     * 保存有未汇总明细的虚拟账号历史
     */
    @Insert("INSERT INTO SELLER_ACCOUNT_HISTORY ( seller_account_id, seller_account, seller_id, " +
            "seller_account_balance, seller_account_income, seller_account_invest, seller_account_commission, seller_account_freeze ) " +
            "SELECT aa.seller_account_id,aa.seller_account,aa.seller_id,aa.account_total,aa.income_total,aa.invest_total,aa.server_amount," +
            "aa.freeze_amount " +
            "FROM seller_account_detail aad " +
            "LEFT JOIN SELLER_ACCOUNT AS aa ON aad.seller_account_id = aa.seller_account_id " +
            "WHERE aad.is_total = 'N' AND aad.pay_time <= now() AND aad.trade_serial IS NOT NULL ")
    int insSellerAccountHistory();

    /**
     * 更新虚拟账号历史
     */
    @Update("<script>" +
            "update SELLER_ACCOUNT aa set aa.account_total = ifnull(aa.account_total,0) + #{item.accountTotal}," +
            "aa.income_total = ifnull(aa.income_total,0) + #{item.incomeTotal},aa.invest_total = ifnull(aa.invest_total,0) + #{item.investTotal}," +
            "aa.server_amount = ifnull(aa.server_amount,0) + #{item.serverAmount} where aa.seller_account_id = #{item.sellerAccountId}" +
            "</script>")
    int updSellerAccount(@Param("item") SellerAccount accountList);

    /**
     * 获取虚拟账号汇总数据
     */
    @Select("select ad.seller_account_id,sum(ad.trade_money*ad.oper_type) as account_total," +
            "sum((case when ad.oper_type = 1 then ad.trade_money else 0 end)) as income_total, " +
            "sum((case when ad.pay_subject = '0003' and ad.pay_type = 1 then ad.trade_money else 0 end)) as invest_total," +
            "sum((case when ad.pay_subject = '0002' and ad.pay_type = 1 then ad.trade_money else 0 end)) as server_amount " +
            "from seller_account_detail ad where ad.is_total = 'N' and ad.pay_time <= now() and ad.trade_serial is not null group by ad.seller_account_id")
    @Results({
            @Result(property = "sellerAccountId",column = "seller_account_id"),
            @Result(property = "accountTotal",column = "account_total"),
            @Result(property = "incomeTotal",column = "income_total"),
            @Result(property = "investTotal",column = "invest_total"),
            @Result(property = "serverAmount",column = "server_amount")
    })
    List<SellerAccount> qryAccountData();

    /**
     * 关闭已经统计的虚拟账号流水
     */
    @Update("update seller_account_detail set is_total='Y', total_time = now() where pay_time <= now() and is_total = 'N' and trade_serial is not null")
    int upSellerAccountDetail();

    /**
     * 订单转入商户发放费用结算
     */
    @Insert("INSERT INTO agent_account_detail ( trade_id, agent_account_id, trade_serial, order_source, oper_type, " +
            "pay_time, pay_subject, pay_type, trade_money, trade_comment, is_total, cpm_time ) " +
            "SELECT serial,virtual_account,order_id,order_source,direction,create_time,sub_code,sub_type,trans_amount," +
            "`COMMENT`,'N',update_time FROM cpm_inside_trans WHERE account_type = 1 AND is_syn = 'N'")
    int insAgentAccountDetail();

    /**
     * 代理商入历史表
     */
    @Insert("INSERT INTO AGENT_ACCOUNT_HISTORY ( agent_account_id, agent_account, agent_id, agent_account_balance, agent_account_income, " +
            "agent_account_pay, agent_account_commission, agent_account_freeze ) " +
            "SELECT aa.agent_account_id,aa.agent_account,aa.agent_id,aa.agent_account_balance,aa.agent_account_income," +
            "aa.agent_account_pay,aa.agent_account_commission,aa.agent_account_freeze " +
            "FROM AGENT_ACCOUNT_DETAIL aad LEFT JOIN AGENT_ACCOUNT AS aa ON aad.agent_account_id = aa.agent_account_id " +
            "WHERE aad.is_total = 'N' AND aad.pay_time <= now() AND aad.trade_serial IS NOT NULL")
    int insAgentAccountHis();

    /**
     * 查询代理商结算金额
     */
    @Select("SELECT ad.agent_account_id,sum( ad.trade_money * ad.oper_type ) as agent_account_balance," +
            "sum((case when ad.oper_type = 1 then ad.trade_money else 0 end)) as agent_account_income," +
            "sum((case when ad.oper_type = -1 then ad.trade_money else 0 end)) as agent_account_pay," +
            "sum((case when ad.pay_subject in ('0004','0005') and ad.pay_type = 0 then ad.trade_money else 0 end)) as agent_account_commission " +
            "FROM AGENT_ACCOUNT_DETAIL ad WHERE ad.is_total = 'N' AND ad.pay_time <= now() AND ad.trade_serial IS NOT NULL group by ad.agent_account_id")
    @Results({
            @Result(property = "agentAccountId",column = "agent_account_id"),
            @Result(property = "agentAccountBalance",column = "agent_account_balance"),
            @Result(property = "agentAccountIncome",column = "agent_account_income"),
            @Result(property = "agentAccountPay",column = "agent_account_pay"),
            @Result(property = "agentAccountCommission",column = "agent_account_commission")
    })
    List<AgentAccount> qryAccounts();

    /**
     * 更新代理商账户金额
     * @param accounts
     * @return
     */
    @Update("<script>" +
            "update AGENT_ACCOUNT aa set " +
            "aa.agent_account_balance = ifnull(aa.agent_account_balance,0) + #{item.agentAccountBalance}," +
            "aa.agent_account_income = ifnull(aa.agent_account_income,0) + #{item.agentAccountIncome}," +
            "aa.agent_account_pay = ifnull(aa.agent_account_pay,0) + #{item.agentAccountPay}," +
            "aa.agent_account_commission = ifnull(aa.agent_account_commission,0) + #{item.agentAccountCommission} " +
            "where aa.agent_account_id = #{item.agentAccountId}" +
            "</script>")
    int updAgentAccount(@Param("item")AgentAccount accounts);

    /**
     * 更新代理商账户状态
     */
    @Update("update AGENT_ACCOUNT_DETAIL set is_total='Y', total_time = now() where pay_time <= now() and is_total = 'N' and trade_serial is not null")
    int updAgentAccountState();
}
